package hello;


import java.util.Collections;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import persistence.HibernateUtil;
import auction.chapter15.ItemDTO;
import auction.model.Bid;
import auction.model.Item;
import auction.model.User;

public class Chapter15AdvancedQuery
{
	public static void main(String[] args)throws Exception{
		//testBasicCriteria();
		//testRestrictions();
		testAdvancedCriteria();
		//testIgnoreFetchStrategy();
		//testQBE();
		//testNativeSQL();
		//testFilterCollections();
		//testQueryCache();
	}
	
	public static void testQueryCache()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		List r=Collections.EMPTY_LIST;
		
		
		/*
		//return entity only cache PK
		//caching query results in region: org.hibernate.cache.StandardQueryCache; timestamp=5626066612297728
		//Item entity --> second level cache
		//PK --> query cache
		r=s.createQuery("from Item i where i.id=1")
					.setCacheable(true)//Enable caching of this query result set.
					//.setCacheRegion("set the name of query cache region")
					.list();
		*/
		
		/*
		//cache the scalar value in cache
		//scalar values --> query cache
		List r=s.createQuery("select title from Item i where i.id=1")
				.setCacheable(true)//Enable caching of this query result set.
				.list();
		*/
		
		//first execute this query
		//Item --> second level cache
		//PK and business key --> query cache
		r=s.createCriteria(Item.class)
				.add(Restrictions.naturalId()
						.set("title", "a")
						.set("title2", "b"))
				.setCacheable(true)
				.list();
		
		tx.commit();
		s.close();
		
		
		
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		//============ Another Session ============
		
		/*
		//if second level cache enabled:
		//  cache lookup: auction.model.Item#1
		//  cache hit
		//if second level cache disabled, hit db again
		r=s1.createQuery("from Item i where i.id=1")
				.setCacheable(true)//MUST have this line to read from cache
				.list();
		*/
		
		/*
		//return scalar value in cache
		//checking cached query results in region: org.hibernate.cache.StandardQueryCache
		//Checking query spaces for up-to-dateness: [item]
		//returning cached query results. !!!GOOD
		r=s1.createQuery("select title from Item i where i.id=1")
				.setCacheable(true)//MUST have this line to read from cache
				.list();
		*/
		
		//first find PK in query cache according to business key
		//then find entity from second level cache according to PK
		r=s1.createCriteria(Item.class)
				.add(Restrictions.naturalId()
						.set("title", "a")
						.set("title2", "b"))
				.setCacheable(true)
				.list();
		
		
		//s1.get(Item.class, 1L);
		
		for(Object obj : r){
			if(obj instanceof Object[]){
				out("AAA");
				Object[] objs =(Object[])obj;
				Item i=(Item)objs[0];
				//out(i.getBids().size());
				for(Object o : objs){
					out(o.getClass().getName());
				}
			}else {
				out("BBB");
				out(obj);
			}
		}
		
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}

	
	public static void testFilterCollections()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		
		Item li=(Item)s.get(Item.class, 1L);
		//out(li.getBids().size());
		
		/*
		//item need in persistent state; detached won't work [The collection was unreferenced]
		List r=s.createFilter(li.getBids(), "")
				.setFirstResult(1)
				.setMaxResults(5)
				.list();
		*/
		
		/*
		//select u.* from bids b inner join user u on b.bidderid=u.userid where b.itemid=1
		List r=s.createFilter(li.getBids(), "select this.bidder")
					.list();
		*/
		
		//with elements: the SQL is different but the result is same
		List r=s.createFilter(li.getBids(), "select elements(this.bidder.bids)")
					.list();
		
		
		/*
		//without elements: the SQL is different but the result is same
		List r=s.createFilter(li.getBids(), "select this.bidder.bids")
				.list();
		*/
		/*
		CategoryChapter12 cat=(CategoryChapter12)s.get(CategoryChapter12.class, 1L);
		String filterStr="select other from CategoryChapter12 other where this.name = other.name";
		//select * from category cat where cat.parentid=this.id
		List r=s.createFilter(cat.getChildCategories(), filterStr)
					.list();
		*/
		
		
		tx.commit();
		s.close();
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		
		
		Item iq=new Item();
		iq.setId(1L);
		iq.setTitle("xxx");
		
		/*
		List r=s1.createQuery("from Bid b where b.item = :givenItem")
					.setEntity("givenItem", iq)
					.list();
		*/
		
		
		for(Object obj : r){
			if(obj instanceof Object[]){
				out("AAA");
				Object[] objs =(Object[])obj;
				Item i=(Item)objs[0];
				//out(i.getBids().size());
				for(Object o : objs){
					out(o.getClass().getName());
				}
			}else{
				out("BBB");
				out(obj.getClass().getName());
			}
		}
		
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	public static void testNativeSQL()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		
		
		tx.commit();
		s.close();
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		/*
		List r=s1.createSQLQuery("select *,1 from item")
					.addEntity(Item.class)
					.list();
		*/
		
		/*
		//i.* will output directly, {i.*} will be replaced with each columns
		List r=s1.createSQLQuery("select {i.*} from item i join bids b on i.itemid=b.itemid_b")
					.addEntity("b", Item.class)
					.list();
		*/
		/*
		//eagerly fetch collection: return Object[], item with bids filled
		List r=s1.createSQLQuery("select {i.*},{b.*} from item i join bids b on i.itemid=b.itemid_b")
					.addEntity("i",Item.class)
					.addJoin("b", "i.bids")
					.list();
		*/
		
		/*
		//eagerly fetch to-one: return Object[], item with seller filled
		List r=s1.createSQLQuery("select {i.*},{u.*} from item i join user u on i.seller_id=u.userid")
					.addEntity("i",Item.class)
					.addJoin("u", "i.seller")
					.list();
		*/
		
		List r=s1.createSQLQuery("select i.title from item i")
					//.addScalar("title")
					.list();
		
		
		for(Object obj : r){
			if(obj instanceof Object[]){
				out("AAA");
				Object[] objs =(Object[])obj;
				Item i=(Item)objs[0];
				//out(i.getBids().size());
				for(Object o : objs){
					out(o.getClass().getName());
				}
			}else{
				out("BBB");
				out(obj.getClass().getName());
			}
		}
		
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	/**
	 * ONLY can express equal property
	 * @throws Exception
	 */
	public static void testQBE()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		
		
		tx.commit();
		s.close();
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		
		Item ie=new Item();
		//i.setId(1L); //excluded
		ie.setTitle("gAme2");
		
		Item ie2=new Item();
		ie2.setTitle2("2");
		
		//no effect
		Bid be=new Bid();
		be.setPrice(100.99);
		ie.getBids().add(be);
		
		//all value-typed, no PK, no entity
		Example e=Example.create(ie)
							.ignoreCase()//lower(title)=game2
							//.enableLike(MatchMode.ANYWHERE)
							;
		List r=s1.createCriteria(Item.class)
		         .add(e)
		         .list();
		
		/*
		List r=s1.createCriteria(Item.class)
					 //this shows how to expression or
					.add(Restrictions.or(Example.create(ie), Example.create(ie2)))
					//.add(Example.create(i))
					.list();
		*/
		
		//mock edit item admin page that find a item has propname is "xxx"
		/*
		List r=s1.createCriteria(Item.class)
					.add(Example.create(ie))
					.createCriteria("bids")
						.add(Example.create(be))
					.list()
					;
		*/
		
		for(Object obj : r){
			out(obj.getClass().getName());
		}
		
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	public static void testAdvancedCriteria()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		
		
		tx.commit();
		s.close();
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		/*
		// select * from item left outer join bids on iid=iid where title like 'ok'
		Criteria c=s1.createCriteria(Item.class)
						.setFetchMode("bids", FetchMode.JOIN)
						.add(Restrictions.like("title", "%a%"))
						;
		*/
		
		/*
		//they are same
		Criteria c=s1.createCriteria(Item.class)
						//.createCriteria("bids", CriteriaSpecification.INNER_JOIN)
						.createAlias("bids", "b", CriteriaSpecification.INNER_JOIN)
						//.setFetchMode("b", FetchMode.JOIN)
						.add(Restrictions.like("title", "%a%"))
						.setResultTransformer(Criteria.ROOT_ENTITY)
						;
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.createAlias("bids", "b")
						//.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						;
		*/
		
		/*
		Query c=s1.createQuery("select i.title as title from Item i");
		c.setResultTransformer(Transformers.aliasToBean(ItemDTO.class));
		*/
		
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.setProjection(Projections.projectionList()
										.add(Projections.id())
										.add(Projections.alias(Projections.property("title"),"title"))
									  )
						.setResultTransformer(Transformers.aliasToBean(ItemDTO.class))
						;
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.setProjection(Projections.projectionList()
										.add(Projections.groupProperty("id"))
										.add(Projections.groupProperty("title"))
										.add(Projections.count("id"))
										
										)
						;
		*/
		
		/*
		String sql="(select count(*) from Item i where i.itemid = itemid_b) as numOfItems";
		Criteria c=s1.createCriteria(Bid.class)
						.setProjection(Projections.projectionList()
										.add(Projections.avg("price"))
										.add(Projections.sqlProjection(sql, new String[]{"numOfItems"}, new Type[]{Hibernate.LONG}))
								);
		*/
		
		/*
		//find max bid
		Item ii=new Item();
		ii.setId(1L);
		Criteria c=s1.createCriteria(Bid.class)
				.add(Restrictions.eq("item", ii))
				.addOrder(Order.desc("price"))
				.setMaxResults(1);
		*/
		
		/*
		//find max bid, the same
		Item li=(Item)s1.load(Item.class, 1L);
		Query c=s1.createFilter(li.getBids(), "order by this.price desc")
					.setMaxResults(1);
		*/
		
		//<many-to-one lazy="false" fetch="join"> when using Projection it is like fetch="select"
		Criteria c=s1.createCriteria(Bid.class)
		                   //.createAlias("item", "item_alias")
		                   //.add(Restrictions.eq("item_alias.id", 1L))
		                   //.add(Restrictions.eq("item", s1.load(Item.class, 1l)))
		                   //.createAlias("seller", "seller_alias",CriteriaSpecification.LEFT_JOIN)
		                   //.add(Restrictions.like("seller_alias.name", "%a%"))
		                   
		                   //.addOrder(Order.desc("seller_alias.id"))
		                   //.addOrder(Order.desc("seller_alias.name"))
		                   
		                   .setProjection(Projections.projectionList()
		                                               //.add(Projections.property("item"))
		                                               .add(Projections.groupProperty("item")) 
		                                  )
		                   
		                   //.setFetchMode("item", FetchMode.JOIN)
		                   //.setProjection(Projections.rowCount())
		                   ;
		c.setFirstResult(0);
		c.setMaxResults(6);
		List r=c.list();
		
		for(Object obj : r){
		    //Hibernate.initialize(obj);
		}
		
		tx1.commit();
        s1.close();
        out("SIZE:"+r.size());
		for(Object obj : r){
			if(obj instanceof Item){
				out("AAAA");
				out(obj.getClass().getName());
				Item i=(Item)obj;
				out(i.getTitle());
				//out(i.getBids());
				//out(i.getClass().getName());
			}else if(obj instanceof Map){
				out("BBBB");
				Map map=(Map)obj;
				out(map.get(Criteria.ROOT_ALIAS));
				out(map.get("b"));
			}else if(obj instanceof ItemDTO){
				out("CCCC");
				out(obj);
			}else if(obj instanceof Bid){
			  out("EEEE");
			  Bid bid = (Bid)obj;
			  out(bid.getItem().getTitle());
			}else{
				out("DDDD "+obj.getClass().getName() );
				out(obj);
			}
			
		}
				
		
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	public static void testRestrictions()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		Item i=new Item("game");
		User seller=new User("wei");
		i.setSeller(seller);
		//s.save(i);
		//s.save(seller);
		
		tx.commit();
		s.close();
		
		
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		Criteria c=s1.createCriteria(Item.class)
		                //where lower(title)='abc'
                        .add(Restrictions.eq("title","AbC").ignoreCase())
                        //order by lower(title)
                        .addOrder(Order.asc("title").ignoreCase())
                        ;
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.add(Restrictions.isEmpty("bids"));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.add(Restrictions.eqProperty("id", "title"));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.add(Restrictions.like("title", "G",MatchMode.START));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.add(Restrictions.or(Restrictions.like("title", "A%"), 
												Restrictions.like("title", "B%")));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.add(Restrictions.disjunction()
								.add(Restrictions.like("title", "A%"))
								.add(Restrictions.like("title", "B%")));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
				.add(Restrictions.sqlRestriction("length({alias}.title) < ?",2,Hibernate.INTEGER));
		*/
		
		/*
		// select u.* from user where 10 > (select count(i.itemid) from item where i.seller_id=u.userid)
		DetachedCriteria subquery=DetachedCriteria.forClass(Item.class,"i")
									.add(Restrictions.eqProperty("i.seller.id", "u.id"))
									.setProjection(Property.forName("i.id").count())
									;
		Criteria c = s1.createCriteria(User.class,"u")
							.add(Subqueries.lt(10, subquery));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.add(Restrictions.like("title", "g%"));
		Criteria bidc=c.createCriteria("bids")
							.add(Restrictions.gt("price", 9.9));
		*/
		
		/*
		Criteria c=s1.createCriteria(Item.class)
						.createAlias("bids", "b")
						.add(Restrictions.like("title", "g%"))
						.add(Restrictions.gt("b.price", 9.9));
		*/
		List r=c.list();
		
		for(Object obj : r){
			out(obj.getClass().getName());
		}
		
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	public static void testBasicCriteria()throws Exception{
		Session s=HibernateUtil.getSessionFactory().openSession();
		Transaction tx=s.beginTransaction();
		
		//Message m=new Message("game");
		//s.save(m);
		
		tx.commit();
		s.close();
		
		Session s1=HibernateUtil.getSessionFactory().openSession();
		Transaction tx1=s1.beginTransaction();
		/*
		Criteria c=s1.createCriteria(Message.class)
						.addOrder(Order.desc("text"))
						.add(Restrictions.isNull("text"))
						;
		*/
		/* Criteria also support polymorphism */
		//Criteria c=s1.createCriteria(Object.class);
		
		/*
		DetachedCriteria dc = DetachedCriteria.forClass(Message.class)
								.addOrder(Order.desc("text"));
		Criteria c = dc.getExecutableCriteria(s1);
		*/
		
		
		/* in spring env, HibernateTemplate accept DetachedCriteria as in parameter */
		
		
		List r=Collections.EMPTY_LIST;
		
		//r=c.list();
		
		for(Object obj : r){
			out(obj.getClass().getName());
		}
		
		
		/*
		Object obj=s1.createCriteria(Message.class)
				.add(Restrictions.eq("id", 1L))
				.uniqueResult();
		*/
				
		tx1.commit();
		s1.close();
		
		
		
		//HibernateUtil.stat();
		HibernateUtil.shutdown();
	}
	
	public static void getParent(Class clazz)
	{
		Class parent=clazz.getSuperclass();
	  if(parent !=null)
	  {
			System.out.println("###"+parent.getName());
			getParent(parent);
		}
	}
	
	static void out(Object obj)
	{
		System.out.println("--->"+obj);
	}
}
